﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using log4net;

namespace LanguageDetector.Data
{
    public static class BiGramStatDB
    {
        static ILog logger = LogManager.GetLogger(typeof(BiGramStatDB));

        const int SAVE_BATCH = 256;

        #region fill method

        public static QueryResult<BiGramStat> Get(BiGramCriteria filter)
        {
            var res = new QueryResult<BiGramStat>();
            try
            {
                ValidateGet(filter, res);
                if (res.Errors.Count > 0)
                    res.Errors.ForEach(e => logger.Warn("Validation Error.", e));
                else
                {
                    var q = CreateGetQuery(filter);
                    res.Errors.AddRange(q.ExecuteReader(dr =>
                    {
                        var s = new BiGramStat();
                        s.ID = dr.GetInt32(0);
                        
                        var a = dr.GetString(1);
                        var b = dr.GetString(2);
                        s.BiGram = new BiGram(a[0], b[0]);

                        s.LanguageID = dr.GetInt32(3);
                        s.Count = dr.GetInt32(4);
                        s.IsDirty = false;
                        res.Add(s);
                    }));
                    res.Errors.ForEach(e => logger.Error("Fill Error.", e));
                }
            }
            catch (Exception ex) 
            {
                res.Errors.Add(ex);
                logger.Error("Error fetching BiGramStats!", ex);
            }
            return res;
        }

        static Query CreateGetQuery(BiGramCriteria filter)
        {
            var sb = new StringBuilder();
            if (!filter.IDs.IsNullOrEmpty())
            {
                sb.Append("id IN (");
                filter.IDs.ForEach(id => sb.AppendFormat("{0},", id));
                sb.Length--;
                sb.Append(") and ");
            }
            if (filter.MinCount > 0)
                sb.AppendFormat("count >= {0} and ", filter.MinCount);
            if (sb.Length > 4)
            {
                sb.Length -= 4;
                sb.Insert(0, "where ");
            }
            sb.Insert(0, "select id,a,b,language,count from bigram_stats ");
            sb.Append(";");
            var q = new Query() { Text = sb.ToString() };
            return q;
        }

        static void ValidateGet(BiGramCriteria filter, QueryResult<BiGramStat> res)
        {
            if (filter == null)
                res.Errors.Add(new ArgumentNullException("filter can not be null"));
        }

        #endregion

        #region save method

        public static List<Exception> Save(List<BiGramStat> sList)
        {
            return Save(sList, true);
        }

        public static List<Exception> Save(List<BiGramStat> sList, bool fetchNewIDs)
        {
            var errors = new List<Exception>();
            try
            {
                ValidateSave(sList, errors);
                if (errors.Count > 0)
                    errors.ForEach(e => logger.Warn("Save validation error", e));
                else
                {
                    var langIDs = new List<int>();
                    var langq = new Query() { Text = "select id from language_stat;" };
                    errors.AddRange(langq.ExecuteReader(dr => langIDs.Add(dr.GetInt32(0))));
                    if (langIDs.IsNullOrEmpty())
                        errors.Add(new DataException("language_stat table is empty or data can't be fetched!"));
                    else
                    {
                        var dirtyList = (from s in sList where s.IsDirty select s).ToList();
                        var batches = dirtyList.ExplodeChunks(SAVE_BATCH);
                        for (var i = 0; i < batches.Count; i++)
                        {
                            var chunk = batches[i];
                            if (fetchNewIDs)
                                SaveFetchNewIDs(chunk, langIDs, errors);
                            else
                                SaveOnly(chunk, langIDs, errors);
                        }
                    }
                    if(errors.Count>0)
                        errors.ForEach(e => logger.Warn("Save error", e));
                }
            }
            catch (Exception ex)
            {
                errors.Add(ex);
                logger.ErrorFormat("Error saving: {0}. {1}", sList, ex);
            }
            return errors;
        }

        static void SaveFetchNewIDs(List<BiGramStat> sList, List<int> langIDs, List<Exception> errors)
        {
            try
            {
                var sb = new StringBuilder("begin transaction;\r\n");
                for (var i = 0; i < sList.Count; i++)
                {
                    var bs = sList[i];
                    if (ValidateSave(bs, langIDs, errors))
                    {
                        if (bs.ID > 0)
                            sb.AppendFormat("update bigram_stats set a='{0}',b='{1}',language={2},count={3} where id={4};\r\nselect {4} as id;\r\n",
                                bs.BiGram.A, bs.BiGram.B, bs.LanguageID, bs.Count, bs.ID);
                        else
                            sb.AppendFormat("insert into bigram_stats (a,b,language,count) values ('{0}','{1}',{2},{3});\r\nselect last_insert_rowid() as id;\r\n",
                                bs.BiGram.A, bs.BiGram.B, bs.LanguageID, bs.Count);
                    }
                }
                sb.Append("commit transaction;\r\n");
                var q = new Query() { Text = sb.ToString() };
                var j = 0;
                errors.AddRange(q.ExecuteReader(dr =>
                {
                    sList[j].ID = dr.GetInt32(0);
                    j++;
                }));
            }
            catch (Exception ex)
            {
                errors.Add(ex);
                logger.Error("Error during save fetch new id.", ex);
            }
        }

        static void SaveOnly(List<BiGramStat> sList, List<int> langIDs, List<Exception> errors)
        {
            try
            {
                var sb = new StringBuilder("begin transaction;\r\n");
                for (var i = 0; i < sList.Count; i++)
                {
                    var bs = sList[i];
                    if (ValidateSave(bs, langIDs, errors))
                    {
                        if (bs.ID > 0)
                            sb.AppendFormat("update bigram_stats set a='{0}',b='{1}',language={2},count={3} where id={4};\r\n",
                                bs.BiGram.A, bs.BiGram.B, bs.LanguageID, bs.Count, bs.ID);
                        else
                            sb.AppendFormat("insert into bigram_stats (a,b,language,count) values ('{0}','{1}',{2},{3});\r\n",
                                bs.BiGram.A, bs.BiGram.B, bs.LanguageID, bs.Count);
                    }
                }
                sb.Append("commit transaction;\r\n");
                var q = new Query() { Text = sb.ToString() };
                errors.AddRange(q.ExecuteNoneReader());
            }
            catch (Exception ex)
            {
                errors.Add(ex);
                logger.Error("Error during save only.", ex);
            }
        }

        static bool ValidateSave(BiGramStat s, List<int> langIDs, List<Exception> errors)
        {
            var b = false;
            if (s == null)
                errors.Add(new ArgumentNullException("Can not save null 's'."));
            else if (s.LanguageID == 0)
                errors.Add(new ArgumentOutOfRangeException("s.LanguageID needs to be greater than 0!"));
            else if (!langIDs.Contains(s.LanguageID))
                errors.Add(new ArgumentOutOfRangeException("s.LanguageID does not map to an existing language!"));
            else
                b = true;
            return b;
        }

        static void ValidateSave(List<BiGramStat> sList, List<Exception> errors)
        {
            if (sList.IsNullOrEmpty())
                errors.Add(new ArgumentNullException("bs can not be null or empty!"));
            var nulls = sList.RemoveAll(s => s == null);
            if (nulls > 0)
                logger.WarnFormat("sList contains {0} null elements. These has been removed before saving.", nulls);
        }

        #endregion

        #region delete method

        public static List<Exception> Delete(List<int> IDs)
        {
            var errors = new List<Exception>();
            try
            {
                ValidateDelete(IDs, errors);
                if (errors.Count > 0)
                    errors.ForEach(e => logger.Warn("Delete validation error:", e));
                else
                {
                    var sb = new StringBuilder("delete from bigram_stats where id IN (");
                    IDs.ForEach(id => sb.AppendFormat("{0},", id));
                    sb.Length--;
                    sb.Append(");");
                    var q = new Query() { Text = sb.ToString() };
                    errors.AddRange(q.ExecuteNoneReader());
                    errors.ForEach(e => logger.Error("Delete error:", e));
                }
            }
            catch (Exception ex)
            {
                errors.Add(ex);
                logger.Error("Error delting.", ex);
            }
            return errors;
        }

        static void ValidateDelete(List<int> IDs, List<Exception> errors)
        {
            if (IDs.IsNullOrEmpty())
                errors.Add(new ArgumentNullException("IDs can not be null or empty!"));
        }

        #endregion
    }
}
